﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using WaveMaster.Holidays.Club.Gateway;
using WaveMaster.Holidays.Club.Domain;
using WaveMaster.Holidays.Club.SQL;

namespace WaveMaster.Holidays.Club.Service
{
    public class UserService : IService
    {
        DataBaseConnectivity m_dataBaseConectivity;

        public UserService(DataBaseConnectivity dataBaseConectivity)
        {
            m_dataBaseConectivity = dataBaseConectivity;
        }
        public UserService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
        }

        public User GetUserByUserCode(string userCode)
        {
            User result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.GetUserByCode(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("usercode", MySqlDbType.VarChar).Value = userCode;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new User(rd);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public User GetUserByUserCodeAndAgentID(string userCode, long taID)
        {
            User result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.GetUserByCodeAndAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("usercode", MySqlDbType.VarChar).Value = userCode;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = taID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new User(rd);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public User GetUserByUserID(long userID)
        {
            User result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.GetUserByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("userid", MySqlDbType.Int32).Value = userID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new User(rd);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public IList LoadAllUser()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.GetAllUser(), m_dataBaseConectivity.InterfaceSqlConnection);
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    User user = new User(rd);
                    result.Add(user);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x); 
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public IList LoadAllUser(TravelAgent travelAgent)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.GetAllUserByTravelAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgent.TravelAgentID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    User user = new User(rd);
                    result.Add(user);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public void InsertUser(User m_user)
        {
            try
            {
                User user = GetUserByUserCode(m_user.UserCode);
                if (user != null) throw new Exception("User Code already exist");

                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.InsertUser(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("usercode", MySqlDbType.VarChar).Value = m_user.UserCode;
                cmd.Parameters.Add("username", MySqlDbType.VarChar).Value = m_user.UserName;
                cmd.Parameters.Add("password", MySqlDbType.VarChar).Value = m_user.Encrypted_Password;
                cmd.Parameters.Add("expirydate", MySqlDbType.Datetime).Value = m_user.ExpiryDate;
                cmd.Parameters.Add("activecode", MySqlDbType.VarChar).Value = m_user.ActiveCode;
                cmd.Parameters.Add("emailid", MySqlDbType.VarChar).Value = m_user.EmailID;
                cmd.Parameters.Add("createndate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("createnby", MySqlDbType.Int32).Value = m_user.CreatenBy;
                cmd.Parameters.Add("modificationdate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("modificationby", MySqlDbType.Int32).Value = m_user.CreatenBy;
                cmd.Parameters.Add("modificationterminal", MySqlDbType.VarChar).Value = m_user.ModificationTerminal;
                //cmd.Parameters.Add("rolemasterid", MySqlDbType.Int32).Value = m_user.Role.RoleMasterID;
                cmd.Parameters.Add("passwordexpired", MySqlDbType.VarChar).Value = m_user.PasswordExpired;
                cmd.Parameters.Add("passwordexpirydays", MySqlDbType.Int32).Value = m_user.PasswordExpiryDays;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = m_user.TravelAgent.TravelAgentID;
                cmd.Parameters.Add("m_userType", MySqlDbType.VarChar).Value = m_user.UserType;

                cmd.Parameters.Add("m_macAddress", MySqlDbType.VarChar).Value = m_user.MACAddress;
                cmd.Parameters.Add("m_printerNameBP", MySqlDbType.VarChar).Value = m_user.PrinterNameBP;
                cmd.Parameters.Add("m_printerNameWC", MySqlDbType.VarChar).Value = m_user.PrinterNameWC;
                
                cmd.Parameters.Add("m_directPrintBP", MySqlDbType.VarChar).Value = m_user.DirectPrintBP;
                cmd.Parameters.Add("m_printerNameKIMS", MySqlDbType.VarChar).Value = m_user.PrinterNameKIMS;
                cmd.Parameters.Add("m_directPrintKIMS", MySqlDbType.VarChar).Value = m_user.DirectPrintKIMS;
                cmd.Parameters.Add("m_directPrintWC", MySqlDbType.VarChar).Value = m_user.DirectPrintWC;


                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.CommandText = UserSQL.GetMaxUserID();
                //LogService.WriteLogExecuteSQLCommand(cmd);
                long maxID = Convert.ToInt64(cmd.ExecuteScalar());
                foreach (int key in m_user.RoleMasters.Keys)
                {
                    RoleMaster roleMastr = (RoleMaster)m_user.RoleMasters[key];
                    cmd.CommandText = UserSQL.InsertUserRole();
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("userid", MySqlDbType.Int32).Value = maxID;
                    cmd.Parameters.Add("rolemasterid", MySqlDbType.Int32).Value = roleMastr.RoleMasterID;
                    //LogService.WriteLogExecuteSQLCommand(cmd);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void UpdateUser(User m_user)
        {
            try
            {
                //Check if exist user code-----------------
                User user = GetUserByUserCode(m_user.UserCode);
                if (user != null)
                    if (user.UserID != m_user.UserID)
                        throw new Exception("User Code already exist");
                //Check if update 'admin' user code-----------------
                user = GetUserByUserID(m_user.UserID);
                if (user.UserCode.ToLower() == "admin")
                    if (user.UserCode.ToLower() != m_user.UserCode.ToLower())
                        throw new Exception("Could not update admin user code");
                if (user.UserCode.ToLower() == "administrator")
                    if (user.UserCode.ToLower() != m_user.UserCode.ToLower())
                        throw new Exception("Could not update admin user code");
                //update process--------------------------------
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.UpdateUser(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("usercode", MySqlDbType.VarChar).Value = m_user.UserCode;
                cmd.Parameters.Add("username", MySqlDbType.VarChar).Value = m_user.UserName;
                cmd.Parameters.Add("password", MySqlDbType.VarChar).Value = m_user.Encrypted_Password;
                cmd.Parameters.Add("expirydate", MySqlDbType.Datetime).Value = m_user.ExpiryDate;
                cmd.Parameters.Add("activecode", MySqlDbType.VarChar).Value = m_user.ActiveCode;
                cmd.Parameters.Add("emailid", MySqlDbType.VarChar).Value = m_user.EmailID;
                cmd.Parameters.Add("modificationdate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("modificationby", MySqlDbType.Int32).Value = m_user.ModificationBy;
                cmd.Parameters.Add("modificationterminal", MySqlDbType.VarChar).Value = m_user.ModificationTerminal;
                cmd.Parameters.Add("userid", MySqlDbType.Int32).Value = m_user.UserID;
                //cmd.Parameters.Add("rolemasterid", MySqlDbType.Int32).Value = m_user.Role.RoleMasterID;
                cmd.Parameters.Add("passwordexpired", MySqlDbType.VarChar).Value = m_user.PasswordExpired;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = m_user.TravelAgent.TravelAgentID;
                cmd.Parameters.Add("m_userType", MySqlDbType.VarChar).Value = m_user.UserType;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.Parameters.Add("m_macAddress", MySqlDbType.VarChar).Value = m_user.MACAddress;
                cmd.Parameters.Add("m_printerNameBP", MySqlDbType.VarChar).Value = m_user.PrinterNameBP;
                cmd.Parameters.Add("m_printerNameWC", MySqlDbType.VarChar).Value = m_user.PrinterNameWC;
                cmd.Parameters.Add("m_directPrintBP", MySqlDbType.VarChar).Value = m_user.DirectPrintBP;
                cmd.Parameters.Add("m_directPrintWC", MySqlDbType.VarChar).Value = m_user.DirectPrintWC;
                cmd.Parameters.Add("m_printerNameKIMS", MySqlDbType.VarChar).Value = m_user.PrinterNameKIMS;
                cmd.Parameters.Add("m_directPrintKIMS", MySqlDbType.VarChar).Value = m_user.DirectPrintKIMS;

                cmd.ExecuteNonQuery();


                cmd.Parameters.Clear();
                cmd.CommandText = UserSQL.DeleteUserRoleByUserID();
                cmd.Parameters.Add("userid", MySqlDbType.Int32).Value = m_user.UserID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
                foreach (int key in m_user.RoleMasters.Keys)
                {
                    RoleMaster roleMastr = (RoleMaster)m_user.RoleMasters[key];
                    cmd.CommandText = UserSQL.InsertUserRole();
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("userid", MySqlDbType.Int32).Value = m_user.UserID;
                    cmd.Parameters.Add("rolemasterid", MySqlDbType.Int32).Value = roleMastr.RoleMasterID;
                    //LogService.WriteLogExecuteSQLCommand(cmd);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void UpdateUserPrinterOptions(User m_user)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.UpdateUserPrinterOptions(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("userid", MySqlDbType.Int32).Value = m_user.UserID;
                cmd.Parameters.Add("m_macAddress", MySqlDbType.VarChar).Value = m_user.MACAddress;
                cmd.Parameters.Add("m_printerNameBP", MySqlDbType.VarChar).Value = m_user.PrinterNameBP;
                cmd.Parameters.Add("m_printerNameWC", MySqlDbType.VarChar).Value = m_user.PrinterNameWC;
                cmd.Parameters.Add("m_directPrintBP", MySqlDbType.VarChar).Value = m_user.DirectPrintBP;
                cmd.Parameters.Add("m_directPrintWC", MySqlDbType.VarChar).Value = m_user.DirectPrintWC;
                cmd.Parameters.Add("m_printerNameKIMS", MySqlDbType.VarChar).Value = m_user.PrinterNameKIMS;
                cmd.Parameters.Add("m_directPrintKIMS", MySqlDbType.VarChar).Value = m_user.DirectPrintKIMS;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void DeleteUser(User m_user)
        {
            try
            {
                if (m_user.UserCode.ToLower() == "admin" || m_user.UserCode.ToLower() == "administrator")
                        throw new Exception("Could not delete admin user code");

                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.DeleteUserRoleByUserID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("userid", MySqlDbType.Int32).Value = m_user.UserID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();
                cmd.CommandText = UserSQL.DeleteUser();
                cmd.Parameters.Add("userid", MySqlDbType.Int32).Value = m_user.UserID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void InsertLog(User m_user)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.CountUserLog(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("sessioncode", MySqlDbType.VarChar).Value = m_user.SESSION_ID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                int found = Convert.ToInt16(cmd.ExecuteScalar());
                if (found == 0)
                {
                    cmd = new MySqlCommand(UserSQL.InsertLogUser(), m_dataBaseConectivity.InterfaceSqlConnection);
                    cmd.Parameters.Add("userid", MySqlDbType.VarChar).Value = m_user.UserID;
                    cmd.Parameters.Add("logindate", MySqlDbType.Datetime).Value = DateTime.Now;
                    cmd.Parameters.Add("sessioncode", MySqlDbType.VarChar).Value = m_user.SESSION_ID;
                    cmd.Parameters.Add("remark", MySqlDbType.VarChar).Value = "User sign in to system";
                    //LogService.WriteLogExecuteSQLCommand(cmd);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void UpdateLogOut(User m_user)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.UpdateLogoutUser(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("logoutdate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("sessioncode", MySqlDbType.VarChar).Value = m_user.SESSION_ID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void ChangePassword(User m_user, string p)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.UpdatePasswordUser(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("password", MySqlDbType.VarChar).Value = m_user.GetEncryptPassword(p);
                cmd.Parameters.Add("expirydate", MySqlDbType.VarChar).Value = DateTime.Now.AddDays(m_user.PasswordExpiryDays);
                cmd.Parameters.Add("modificationdate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("modificationby", MySqlDbType.Int32).Value = m_user.ModificationBy;
                cmd.Parameters.Add("modificationterminal", MySqlDbType.VarChar).Value = m_user.ModificationTerminal;
                cmd.Parameters.Add("userid", MySqlDbType.Int32).Value = m_user.UserID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void DeleteRoleMaster(RoleMaster m_roleMaster)
        {
            try
            {
                //delete details----
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.DeleteRoleDetilByRoleMaster(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("rolemasterid", MySqlDbType.Int32).Value = m_roleMaster.RoleMasterID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
                //delete header----
                cmd = new MySqlCommand(UserSQL.DeleteRoleMaster(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("rolemasterid", MySqlDbType.Int32).Value = m_roleMaster.RoleMasterID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void InsertRoleMaster(RoleMaster m_roleMaster)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.InsertRoleMaster(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("rolemastername", MySqlDbType.VarChar).Value = m_roleMaster.RoleName;
                cmd.Parameters.Add("cretndate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("cretnby", MySqlDbType.Int32).Value = m_roleMaster.CreatenBy;
                cmd.Parameters.Add("modfctndate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("modfctnby", MySqlDbType.Int32).Value = m_roleMaster.ModificationBy;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.CommandText = UserSQL.GetMaxRoleMasterID();
                //LogService.WriteLogExecuteSQLCommand(cmd);
                long maxID = Convert.ToInt64(cmd.ExecuteScalar());
                foreach (string key in m_roleMaster.RoleDetails.Keys)
                {
                    RoleDetail detl = (RoleDetail)m_roleMaster.RoleDetails[key];
                    cmd.CommandText = UserSQL.InsertRoleDetil();
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("rolemasterid", MySqlDbType.Int32).Value = maxID;
                    cmd.Parameters.Add("viewflag", MySqlDbType.VarChar).Value = detl.ViewFlag;
                    cmd.Parameters.Add("newflag", MySqlDbType.VarChar).Value = detl.NewFlag;
                    cmd.Parameters.Add("updateflag", MySqlDbType.VarChar).Value = detl.UpdateFlag;
                    cmd.Parameters.Add("formname", MySqlDbType.VarChar).Value = detl.FormName;
                    cmd.Parameters.Add("deleteflag", MySqlDbType.VarChar).Value = detl.DeleteFlag;
                    //LogService.WriteLogExecuteSQLCommand(cmd);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void UpdateRoleMaster(RoleMaster m_roleMaster)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.UpdateRoleMaster(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("rolemastername", MySqlDbType.VarChar).Value = m_roleMaster.RoleName;
                cmd.Parameters.Add("modfctndate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("modfctnby", MySqlDbType.Int32).Value = m_roleMaster.ModificationBy;
                cmd.Parameters.Add("rolemasterid", MySqlDbType.Int32).Value = m_roleMaster.RoleMasterID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.CommandText = UserSQL.DeleteRoleDetilByRoleMaster();
                cmd.Parameters.Add("rolemasterid", MySqlDbType.Int32).Value = m_roleMaster.RoleMasterID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
                foreach (string key in m_roleMaster.RoleDetails.Keys)
                {
                    RoleDetail detl = (RoleDetail)m_roleMaster.RoleDetails[key];
                    cmd.CommandText = UserSQL.InsertRoleDetil();
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("rolemasterid", MySqlDbType.Int32).Value = m_roleMaster.RoleMasterID;
                    cmd.Parameters.Add("viewflag", MySqlDbType.VarChar).Value = detl.ViewFlag;
                    cmd.Parameters.Add("newflag", MySqlDbType.VarChar).Value = detl.NewFlag;
                    cmd.Parameters.Add("updateflag", MySqlDbType.VarChar).Value = detl.UpdateFlag;
                    cmd.Parameters.Add("formname", MySqlDbType.VarChar).Value = detl.FormName;
                    cmd.Parameters.Add("deleteflag", MySqlDbType.VarChar).Value = detl.DeleteFlag;
                    //LogService.WriteLogExecuteSQLCommand(cmd);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public IList LoadAllRoleMaster()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.GetAllRoleMaster(), m_dataBaseConectivity.InterfaceSqlConnection);
                MySqlCommand cmdDetl = new MySqlCommand(UserSQL.GetRoleDetilByRoleMaster(), m_dataBaseConectivity.InterfaceSqlConnection_b);

                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    RoleMaster rolemaster = new RoleMaster(rd);
                    cmdDetl.Parameters.Clear();
                    cmdDetl.Parameters.Add("rolemasterid", MySqlDbType.Int32).Value = rolemaster.RoleMasterID;
                    //LogService.WriteLogExecuteSQLCommand(cmd);
                    MySqlDataReader detRD = cmdDetl.ExecuteReader();
                    while (detRD.Read())
                    {
                        RoleDetail roleDetil = new RoleDetail(detRD);
                        roleDetil.RoleMaster = rolemaster;
                        rolemaster.AddRoleDetails(roleDetil);
                    }
                    detRD.Close();
                    result.Add(rolemaster);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }
            return result;
        }

        public RoleMaster GetRoleMasterByID(long id)
        {
            RoleMaster rolemaster = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.GetRoleMasterByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("rolemasterid", MySqlDbType.Int32).Value = id;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    rolemaster = new RoleMaster(rd);
                    MySqlCommand cmdDetl = new MySqlCommand(UserSQL.GetRoleDetilByRoleMaster(), m_dataBaseConectivity.InterfaceSqlConnection_b);
                    cmdDetl.Parameters.Add("rolemasterid", MySqlDbType.Int32).Value = rolemaster.RoleMasterID;
                    //LogService.WriteLogExecuteSQLCommand(cmd);
                    MySqlDataReader detRD = cmdDetl.ExecuteReader();
                    while (detRD.Read())
                    {
                        RoleDetail roleDetil = new RoleDetail(detRD);
                        roleDetil.RoleMaster = rolemaster;
                        rolemaster.AddRoleDetails(roleDetil);
                    }
                    detRD.Close();
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }
            return rolemaster;
        }

        public void GetUserRoleMasterByUserID(User user)
        {
            RoleMaster rolemaster = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Open();
                m_dataBaseConectivity.InterfaceSqlConnection_c.Open();
                user.RoleMasters.Clear();

                MySqlCommand cmd = new MySqlCommand(UserSQL.GetUserRoleByUserID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("userid", MySqlDbType.Int32).Value = user.UserID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                cmd.Parameters.Clear();

                while (rd.Read())
                {
                    long rmid = Convert.ToInt64(rd["role_master_id"]);
                    cmd = new MySqlCommand(UserSQL.GetRoleMasterByID(), m_dataBaseConectivity.InterfaceSqlConnection_b);
                    cmd.Parameters.Add("rolemasterid", MySqlDbType.Int32).Value = rmid;
                    //LogService.WriteLogExecuteSQLCommand(cmd);
                    MySqlDataReader rdrm = cmd.ExecuteReader();
                    while (rdrm.Read())
                    {
                        rolemaster = new RoleMaster(rdrm);
                        MySqlCommand cmdDetl = new MySqlCommand(UserSQL.GetRoleDetilByRoleMaster(), m_dataBaseConectivity.InterfaceSqlConnection_c);
                        cmdDetl.Parameters.Add("rolemasterid", MySqlDbType.Int32).Value = rolemaster.RoleMasterID;
                        //LogService.WriteLogExecuteSQLCommand(cmd);
                        MySqlDataReader detRD = cmdDetl.ExecuteReader();
                        while (detRD.Read())
                        {
                            RoleDetail roleDetil = new RoleDetail(detRD);
                            roleDetil.RoleMaster = rolemaster;
                            rolemaster.AddRoleDetails(roleDetil);
                        }
                        detRD.Close();
                        user.AddRoleMaster(rolemaster);
                    }
                    rdrm.Close();
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_c.Close();

            }
        }

        public IList SearchUserByUserCodeName(string userCode)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.SearchUserByCodeName(userCode), m_dataBaseConectivity.InterfaceSqlConnection);
                //cmd.Parameters.Add("usercode", MySqlDbType.VarChar).Value = userCode;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new User(rd));
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList SearchUserByUserCodeName(string userCode, TravelAgent ta)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.SearchUserByCodeNameAndTravelAgent(userCode), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = ta.TravelAgentID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new User(rd));
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public IList SearchRoleMasterByCodeName(string userCode)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.SearchRoleMasterByCodeName(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("name", MySqlDbType.VarChar).Value = userCode;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new RoleMaster(rd));
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public user_history AddUserHistory(User user, string session, string remark)
        {
            user_history result = new user_history();
            try
            {
                result.user_id = user.UserID;
                result.start_date = DateTime.Now;
                result.end_date = new DateTime(1900, 1, 1);
                result.session_code = session;
                result.remarks = remark;
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.AddUserHistory(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("user_id", MySqlDbType.Int32).Value = result.user_id;
                cmd.Parameters.Add("start_date", MySqlDbType.Datetime).Value = result.start_date;
                cmd.Parameters.Add("end_date", MySqlDbType.Datetime).Value = result.end_date;
                cmd.Parameters.Add("session_code", MySqlDbType.VarChar).Value = result.session_code;
                cmd.Parameters.Add("remarks", MySqlDbType.VarChar).Value = result.remarks;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.CommandText = UserSQL.GetMaxUserHistoryID();
                result.id = Convert.ToInt64(cmd.ExecuteScalar());
            }
            catch (Exception x)
            {
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public void UpdateEndDate(user_history his)
        {
            try
            {
                his.end_date = DateTime.Now;
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(UserSQL.UpdateEndDateUserHistory(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("id", MySqlDbType.Int64).Value = his.id;
                cmd.Parameters.Add("end_date", MySqlDbType.Datetime).Value = his.end_date;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
    }
}
